package com.ruoyi.web.controller.system;

import java.math.BigDecimal;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.time.LocalDate;
import java.util.*;
import javax.servlet.http.HttpServletResponse;

import com.ruoyi.common.core.domain.entity.SysRole;
import com.ruoyi.common.core.domain.entity.SysUser;
import com.ruoyi.common.utils.SecurityUtils;
import com.ruoyi.system.domain.vo.BaseDataVo;
import com.ruoyi.system.domain.vo.FugouVo;
import org.springframework.security.access.prepost.PreAuthorize;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.web.bind.annotation.GetMapping;
import org.springframework.web.bind.annotation.PostMapping;
import org.springframework.web.bind.annotation.PutMapping;
import org.springframework.web.bind.annotation.DeleteMapping;
import org.springframework.web.bind.annotation.PathVariable;
import org.springframework.web.bind.annotation.RequestBody;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RestController;
import com.ruoyi.common.annotation.Log;
import com.ruoyi.common.core.controller.BaseController;
import com.ruoyi.common.core.domain.AjaxResult;
import com.ruoyi.common.enums.BusinessType;
import com.ruoyi.system.domain.SysWeeklyZcmplan;
import com.ruoyi.system.service.ISysWeeklyZcmplanService;
import com.ruoyi.common.utils.poi.ExcelUtil;
import com.ruoyi.common.core.page.TableDataInfo;

/**
 * SysWeeklyZcmplanController
 * 
 * @author SYF
 * @date 2025-06-17
 */
@RestController
@RequestMapping("/system/weeklyplan")
public class SysWeeklyZcmplanController extends BaseController {

    @Autowired
    private ISysWeeklyZcmplanService sysWeeklyZcmplanService;

    @GetMapping("/")
    public AjaxResult listYearMonth(SysWeeklyZcmplan plan){
        List<Map<String,String>> list = sysWeeklyZcmplanService.selectWeeks(plan.getWkYear());
        Map<String,Object> res = new HashMap();
        res.put("wkList", list);
        return AjaxResult.success();
    }

    @GetMapping("/syfapi/listBaseData")
    public AjaxResult listBaseData(BaseDataVo vo){
        String sql = "SELECT cls_group, base_home, dpt, bigname, sum(qty) as nums, sum(money) as total_price FROM tmp.base_data WHERE 1=1 ";
        if(vo.getYear()!=null){
            sql += " and q_y='"+vo.getYear()+"'";
        }
        if(vo.getMonth()!=null){
            sql += " and q_m='"+vo.getMonth()+"'";
        }
        if(vo.getDay()!=null){
            sql += " and q_q='"+vo.getDay()+"'";
        }
        if(vo.getWeek()!=null){
            sql += " and q_w='"+vo.getWeek()+"'";
        }
        sql += " GROUP BY 1,2,3,4";
        List<Map<String,Object>> list = sysWeeklyZcmplanService.listProductBase(sql);
        return AjaxResult.success(list);
    }

    @GetMapping("/listWeeks4TargetDash")
    public AjaxResult listWeeks4TargetDash(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        List<Map<String,String>> list = sysWeeklyZcmplanService.selectWeeks(year);
        Map<String,Object> res = new HashMap();
        String wkNo = null;
        if(list.size()>0){
            wkNo = list.get(0).get("value");
        }
        List<Map<String,String>> yearList = sysWeeklyZcmplanService.selectYears();
        String wkYear = null;
        if(yearList.size()>0){
            wkYear = yearList.get(0).get("value");
        }
        res.put("wkNo", wkNo);
        res.put("wkList", list);
        res.put("yearList", yearList);
        res.put("wkYear", wkYear);
        return AjaxResult.success(res);
    }

    public Map<String,Object> converDataToMap(List<Map<String,Object>> ymd,String brand){
        Map<String,Object> map = new HashMap<>();
        map.put("bigname", brand);
        if(ymd.size()>0){
            if (new BigDecimal(ymd.get(0).get("target_sum").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sum").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sum", 0);
                map.put("achieve_sum", 0);
                map.put("rate_sum", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sum").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sum").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sum", new BigDecimal(ymd.get(0).get("target_sum").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sum", 0);
                map.put("rate_sum", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sum").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sum").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sum", 0);
                map.put("achieve_sum", new BigDecimal(ymd.get(0).get("achieve_sum").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_sum", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_sum").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sum").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sum", new BigDecimal(ymd.get(0).get("target_sum").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sum", new BigDecimal(ymd.get(0).get("achieve_sum").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_sum").toString()).divide(new BigDecimal(ymd.get(0).get("target_sum").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_sum", rate);
            }
            if (new BigDecimal(ymd.get(0).get("target_sm").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sm").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sm", 0);
                map.put("achieve_sm", 0);
                map.put("rate_sm", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sm").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sm").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sm", new BigDecimal(ymd.get(0).get("target_sm").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sm", 0);
                map.put("rate_sm", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sm").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sm").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sm", 0);
                map.put("achieve_sm", new BigDecimal(ymd.get(0).get("achieve_sm").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_sm", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_sm").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sm").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sm", new BigDecimal(ymd.get(0).get("target_sm").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sm", new BigDecimal(ymd.get(0).get("achieve_sm").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_sm").toString()).divide(new BigDecimal(ymd.get(0).get("target_sm").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_sm", rate);
            }
            if (new BigDecimal(ymd.get(0).get("target_yl").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_yl").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_yl", 0);
                map.put("achieve_yl", 0);
                map.put("rate_yl", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_yl").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_yl").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_yl", new BigDecimal(ymd.get(0).get("target_yl").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_yl", 0);
                map.put("rate_yl", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_yl").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_yl").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_yl", 0);
                map.put("achieve_yl", new BigDecimal(ymd.get(0).get("achieve_yl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_yl", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_yl").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_yl").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_yl", new BigDecimal(ymd.get(0).get("target_yl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_yl", new BigDecimal(ymd.get(0).get("achieve_yl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_yl").toString()).divide(new BigDecimal(ymd.get(0).get("target_yl").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_yl", rate);
            }

            if (new BigDecimal(ymd.get(0).get("target_cg").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_cg").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_cg", 0);
                map.put("achieve_cg", 0);
                map.put("rate_cg", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_cg").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_cg").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_cg", new BigDecimal(ymd.get(0).get("target_cg").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_cg", 0);
                map.put("rate_cg", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_cg").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_cg").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_cg", 0);
                map.put("achieve_cg", new BigDecimal(ymd.get(0).get("achieve_cg").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_cg", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_cg").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_cg").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_cg", new BigDecimal(ymd.get(0).get("target_cg").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_cg", new BigDecimal(ymd.get(0).get("achieve_cg").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_cg").toString()).divide(new BigDecimal(ymd.get(0).get("target_cg").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_cg", rate);
            }
            if (new BigDecimal(ymd.get(0).get("target_cx").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_cx").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_cx", 0);
                map.put("achieve_cx", 0);
                map.put("rate_cx", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_cx").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_cx").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_cx", new BigDecimal(ymd.get(0).get("target_cx").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_cx", 0);
                map.put("rate_cx", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_cx").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_cx").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_cx", 0);
                map.put("achieve_cx", new BigDecimal(ymd.get(0).get("achieve_cx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_cx", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_cx").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_cx").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_cx", new BigDecimal(ymd.get(0).get("target_cx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_cx", new BigDecimal(ymd.get(0).get("achieve_cx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_cx").toString()).divide(new BigDecimal(ymd.get(0).get("target_cx").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_cx", rate);
            }
            if (new BigDecimal(ymd.get(0).get("target_jn").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_jn").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_jn", 0);
                map.put("achieve_jn", 0);
                map.put("rate_jn", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_jn").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_jn").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_jn", new BigDecimal(ymd.get(0).get("target_jn").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_jn", 0);
                map.put("rate_jn", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_jn").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_jn").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_jn", 0);
                map.put("achieve_jn", new BigDecimal(ymd.get(0).get("achieve_jn").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_jn", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_jn").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_jn").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_jn", new BigDecimal(ymd.get(0).get("target_jn").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_jn", new BigDecimal(ymd.get(0).get("achieve_jn").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_jn").toString()).divide(new BigDecimal(ymd.get(0).get("target_jn").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_jn", rate);
            }

            if (new BigDecimal(ymd.get(0).get("target_yx").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_yx").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_yx", 0);
                map.put("achieve_yx", 0);
                map.put("rate_yx", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_yx").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_yx").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_yx", new BigDecimal(ymd.get(0).get("target_yx").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_yx", 0);
                map.put("rate_yx", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_yx").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_yx").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_yx", 0);
                map.put("achieve_yx", new BigDecimal(ymd.get(0).get("achieve_yx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_yx", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_yx").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_yx").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_yx", new BigDecimal(ymd.get(0).get("target_yx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_yx", new BigDecimal(ymd.get(0).get("achieve_yx").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_yx").toString()).divide(new BigDecimal(ymd.get(0).get("target_yx").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_yx", rate);
            }

            if (new BigDecimal(ymd.get(0).get("target_sw").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sw").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sw", 0);
                map.put("achieve_sw", 0);
                map.put("rate_sw", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sw").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sw").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sw", new BigDecimal(ymd.get(0).get("target_sw").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sw", 0);
                map.put("rate_sw", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sw").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sw").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sw", 0);
                map.put("achieve_sw", new BigDecimal(ymd.get(0).get("achieve_sw").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_sw", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_sw").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sw").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sw", new BigDecimal(ymd.get(0).get("target_sw").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sw", new BigDecimal(ymd.get(0).get("achieve_sw").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_sw").toString()).divide(new BigDecimal(ymd.get(0).get("target_sw").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_sw", rate);
            }

            if (new BigDecimal(ymd.get(0).get("target_sl").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sl").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sl", 0);
                map.put("achieve_sl", 0);
                map.put("rate_sl", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sl").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sl").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_sl", new BigDecimal(ymd.get(0).get("target_sl").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sl", 0);
                map.put("rate_sl", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_sl").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_sl").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sl", 0);
                map.put("achieve_sl", new BigDecimal(ymd.get(0).get("achieve_sl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_sl", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_sl").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_sl").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_sl", new BigDecimal(ymd.get(0).get("target_sl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_sl", new BigDecimal(ymd.get(0).get("achieve_sl").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_sl").toString()).divide(new BigDecimal(ymd.get(0).get("target_sl").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_sl", rate);
            }

            if (new BigDecimal(ymd.get(0).get("target_an").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_an").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_an", 0);
                map.put("achieve_an", 0);
                map.put("rate_an", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_an").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_an").toString()).compareTo(new BigDecimal(0))==0){
                map.put("target_an", new BigDecimal(ymd.get(0).get("target_an").toString()).setScale(2, BigDecimal.ROUND_HALF_UP));
                map.put("achieve_an", 0);
                map.put("rate_an", 0);
            }else if(new BigDecimal(ymd.get(0).get("target_an").toString()).compareTo(new BigDecimal(0))==0&&new BigDecimal(ymd.get(0).get("achieve_an").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_an", 0);
                map.put("achieve_an", new BigDecimal(ymd.get(0).get("achieve_an").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("rate_an", 100);
            }else if(new BigDecimal(ymd.get(0).get("target_an").toString()).compareTo(new BigDecimal(0))>0&&new BigDecimal(ymd.get(0).get("achieve_an").toString()).compareTo(new BigDecimal(0))>0){
                map.put("target_an", new BigDecimal(ymd.get(0).get("target_an").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                map.put("achieve_an", new BigDecimal(ymd.get(0).get("achieve_an").toString()).setScale(2,BigDecimal.ROUND_HALF_UP));
                BigDecimal rate = new BigDecimal(ymd.get(0).get("achieve_an").toString()).divide(new BigDecimal(ymd.get(0).get("target_an").toString()), 4, BigDecimal.ROUND_HALF_UP).multiply(new BigDecimal(100)).setScale(2,BigDecimal.ROUND_HALF_UP);
                map.put("rate_an", rate);
            }
        }else{
            map.put("target_sum", 0);
            map.put("achieve_sum", 0);
            map.put("rate_sum", 0);
            map.put("target_sm", 0);
            map.put("achieve_sm", 0);
            map.put("rate_sm", 0);
            map.put("target_yl", 0);
            map.put("achieve_yl", 0);
            map.put("rate_yl", 0);
            map.put("target_cg", 0);
            map.put("achieve_cg", 0);
            map.put("rate_cg", 0);
            map.put("target_cx", 0);
            map.put("achieve_cx", 0);
            map.put("rate_cx", 0);
            map.put("target_jn", 0);
            map.put("achieve_jn", 0);
            map.put("rate_jn", 0);
            map.put("target_yx", 0);
            map.put("achieve_yx", 0);
            map.put("rate_yx", 0);
            map.put("target_sw", 0);
            map.put("achieve_sw", 0);
            map.put("rate_sw", 0);
            map.put("target_sl", 0);
            map.put("achieve_sl", 0);
            map.put("rate_sl", 0);
            map.put("target_an", 0);
            map.put("achieve_an", 0);
            map.put("rate_an", 0);
        }
        return map;
    }

    @GetMapping("/listAreaTargetSummary")
    public AjaxResult listAreaTargetSummary(){
        SysUser user = SecurityUtils.getLoginUser().getUser();
        List<SysRole> roles = user.getRoles();
        String area = "";
        for (SysRole role : roles) {
            if(role.getRoleKey().startsWith("xsry")){
                area = role.getRoleName();
            }
        }
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        SimpleDateFormat sdf1 = new SimpleDateFormat("MM");
        String month = sdf1.format(new Date());

        String sql_yt = "select area,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished_rate*100,2) as rate from bigscreen.syf_dept_area_target where type=0 and area='"+area+"' and `year`='"+year+"'";
        List<Map<String,Object>> yt = sysWeeklyZcmplanService.getChList(sql_yt);

        String sql_mt = "select concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as rate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and area='"+area+"' and `year`='"+year+"' order by month asc";
        List<Map<String,Object>> mt = sysWeeklyZcmplanService.getChList(sql_mt);

        String sql_yjy = "select `name` as brand,ROUND(finished/10000,2) as finished,amount,orders from bigscreen.syf_product_target where type=0 and area='"+area+"' and datatype=0 and `year`='"+year+"'";
        List<Map<String,Object>> yyj = sysWeeklyZcmplanService.getChList(sql_yjy);

        String sql_yzcm = "select bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and area='"+area+"' and datatype=1 and name='自产苗' and `year`='"+year+"'";
        List<Map<String,Object>> yzcm = sysWeeklyZcmplanService.getChList(sql_yzcm);

        String sql_yqt = "select bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and area='"+area+"' and datatype=1 and name='其他' and `year`='"+year+"'";
        List<Map<String,Object>> yqt = sysWeeklyZcmplanService.getChList(sql_yqt);
        String sqlm = "select distinct `month` as value,concat(`year`,'年',`month`,'月') as label from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=0 and `year`='"+year+"' ORDER BY `month` desc";
        List<Map<String,Object>> months = sysWeeklyZcmplanService.getChList(sqlm);

        String sqlwk = "select distinct wk as value,wk as label from weekly_zcmplan where wk_year like '%"+year+"%' order by wk desc";
        List<Map<String,Object>> weeks = sysWeeklyZcmplanService.getChList(sqlwk);

        Map<String,Object> res = new HashMap<>();
        res.put("area", area);
        res.put("yeartarget", yt.get(0));
        res.put("monthtarget", mt);
        res.put("yyj", yyj);
        res.put("yzcm", yzcm);
        res.put("yqt", yqt);
        res.put("month", month);
        res.put("months", months);
        res.put("weeks", weeks);
        res.put("week", weeks.get(0).get("value"));
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaWeekData")
    public AjaxResult listAreaWeekData(FugouVo vo){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        logger.info("-------------year---------"+year);
        logger.info("-------------dpt---------"+vo.getDpt());
        logger.info("-------------week---------"+vo.getWeek());

        String sql_zcm_detail = "select bigname,tge_money as target,ROUND(ht_money,2) as htfinished,ROUND(ht_money/tge_money*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname not in('其他','洋桔梗','洋牡丹')";
        String sql_zcm_sum = "select '合计' as bigname,sum(tge_money) as target,ROUND(sum(ht_money),2) as htfinished,ROUND(sum(ht_money)/sum(tge_money)*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname not in('其他','洋桔梗','洋牡丹')";
        List<Map<String,Object>> zcm_detail = sysWeeklyZcmplanService.getChList(sql_zcm_detail);
        List<Map<String,Object>> zcm_sum = sysWeeklyZcmplanService.getChList(sql_zcm_sum);
        List<Map<String,Object>> zcm = new ArrayList<>();
        if(zcm_detail.size()>0){
            zcm.addAll(zcm_detail);
            zcm.addAll(zcm_sum);
        }

        String sql_qt_detail = "select bigname,tge_money as target,ROUND(ht_money,2) as htfinished,ROUND(ht_money/tge_money*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname='其他'";
        String sql_qt_sum = "select '合计' as bigname,sum(tge_money) as target,ROUND(sum(ht_money),2) as htfinished,ROUND(sum(ht_money)/sum(tge_money)*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname='其他'";
        List<Map<String,Object>> qt_detail = sysWeeklyZcmplanService.getChList(sql_qt_detail);
        List<Map<String,Object>> qt_sum = sysWeeklyZcmplanService.getChList(sql_qt_sum);
        List<Map<String,Object>> qt = new ArrayList<>();
        if(qt_detail.size()>0){
            qt.addAll(qt_detail);
            qt.addAll(qt_sum);
        }

        String sql_zm_detail = "select bigname,tge_money as target,ROUND(ht_money,2) as htfinished,ROUND(ht_money/tge_money*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname in('洋桔梗','洋牡丹')";
        String sql_zm_sum = "select '合计' as bigname,sum(tge_money) as target,ROUND(sum(ht_money),2) as htfinished,ROUND(sum(ht_money)/sum(tge_money)*100,2) as rate from weekly_zcmplan where wk_year like '%"+year+"%' and wk='"+vo.getWeek()+"' and dpt='"+vo.getDpt()+"' and bigname in('洋桔梗','洋牡丹')";

        List<Map<String,Object>> zm_detail = sysWeeklyZcmplanService.getChList(sql_zm_detail);
        List<Map<String,Object>> zm_sum = sysWeeklyZcmplanService.getChList(sql_zm_sum);
        List<Map<String,Object>> zm = new ArrayList<>();
        if(zm_detail.size()>0){
            zm.addAll(zm_detail);
            zm.addAll(zm_sum);
        }

        Map<String,Object> res = new HashMap<>();
        res.put("zcm",zcm);
        res.put("qt",qt);
        res.put("zm",zm);
        return AjaxResult.success(res);
    }

    @GetMapping("/listMonthProductData")
    public AjaxResult listMonthProductData(FugouVo vo){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String brandsql = "select name as brand,ROUND(finished/10000,2) as finished,amount,orders from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=0 and `year`='"+year+"' and `month`='"+vo.getMonth()+"'";
        String zcmsql = "select bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=1 and `year`='"+year+"' and name='自产苗' and `month`='"+vo.getMonth()+"' ORDER BY finished desc";
        String qtsql = "select bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=1 and `year`='"+year+"' and name='其他' and `month`='"+vo.getMonth()+"' ORDER BY finished desc";
        List<Map<String,Object>> brand = sysWeeklyZcmplanService.getChList(brandsql);
        List<Map<String,Object>> zcm = sysWeeklyZcmplanService.getChList(zcmsql);
        List<Map<String,Object>> qt = sysWeeklyZcmplanService.getChList(qtsql);
        Map<String,Object> res = new HashMap<>();
        res.put("brand", brand);
        res.put("zcm", zcm);
        res.put("qt", qt);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaYearProductData")
    public AjaxResult listAreaYearProductData(FugouVo vo){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String brandsql = "SELECT name as brand,ROUND(finished/10000,2) as finished,amount,orders from bigscreen.syf_product_target where type=0 and bigdpt='销售部' and area!='销售部' and datatype=0 and `year`='"+year+"' and area='"+vo.getDpt()+"'";
        String zcmsql = "SELECT bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and bigdpt='销售部' and area!='销售部' and datatype=1 and `year`='"+year+"' and area='"+vo.getDpt()+"' and name='自产苗' order by finished desc";
        String qtsql = "SELECT bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and bigdpt='销售部' and area!='销售部' and datatype=1 and `year`='"+year+"' and area='"+vo.getDpt()+"' and name='其他' order by finished desc";
        List<Map<String,Object>> brand = sysWeeklyZcmplanService.getChList(brandsql);
        List<Map<String,Object>> zcm = sysWeeklyZcmplanService.getChList(zcmsql);
        List<Map<String,Object>> qt = sysWeeklyZcmplanService.getChList(qtsql);
        List<String> bcolors = new ArrayList<>();
        List<String> zcolors = new ArrayList<>();
        List<String> qcolors = new ArrayList<>();
        if(brand.size()>0){
            for(Map<String,Object> map:brand){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                bcolors.add(hexColor);
            }
        }
        if(zcm.size()>0){
            for(Map<String,Object> map:zcm){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                zcolors.add(hexColor);
            }
        }
        if(qt.size()>0){
            for(Map<String,Object> map:qt){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                qcolors.add(hexColor);
            }
        }
        Map<String,Object> res = new HashMap<>();
        res.put("brand", brand);
        res.put("zcm", zcm);
        res.put("qt", qt);
        res.put("bcolors", bcolors);
        res.put("zcolors", zcolors);
        res.put("qcolors", qcolors);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaMonthsDataInfo")
    public AjaxResult listAreaMonthsDataInfo(FugouVo vo) {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String brandsql = "SELECT name as brand,ROUND(finished/10000,2) as finished,amount,orders from bigscreen.syf_product_target where type=1 and bigdpt='销售部' and area not in('销售部','何文丽','洋丽人') and datatype=0 and `year`='"+year+"' and area='"+vo.getDpt()+"' and `month`='"+vo.getMonth()+"'";
        String zcmsql = "SELECT bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=1 and bigdpt='销售部' and area not in('销售部','何文丽','洋丽人') and datatype=1 and `year`='"+year+"' and area='"+vo.getDpt()+"' and name='自产苗' and `month`='"+vo.getMonth()+"' order by finished desc";
        String qtsql = "SELECT bigname as brand,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=1 and bigdpt='销售部' and area not in('销售部','何文丽','洋丽人') and datatype=1 and `year`='"+year+"' and area='"+vo.getDpt()+"' and name='其他' and `month`='"+vo.getMonth()+"' order by finished desc";
        List<Map<String,Object>> brand = sysWeeklyZcmplanService.getChList(brandsql);
        List<Map<String,Object>> zcm = sysWeeklyZcmplanService.getChList(zcmsql);
        List<Map<String,Object>> qt = sysWeeklyZcmplanService.getChList(qtsql);
        List<String> bcolors = new ArrayList<>();
        List<String> zcolors = new ArrayList<>();
        List<String> qcolors = new ArrayList<>();
        if(brand.size()>0){
            for(Map<String,Object> map:brand){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                bcolors.add(hexColor);
            }
        }
        if(zcm.size()>0){
            for(Map<String,Object> map:zcm){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                zcolors.add(hexColor);
            }
        }
        if(qt.size()>0){
            for(Map<String,Object> map:qt){
                Random random = new Random();
                // 生成随机 RGB 值
                int red = random.nextInt(256);
                int green = random.nextInt(256);
                int blue = random.nextInt(256);
                String hexColor = String.format("#%02X%02X%02X", red, green, blue);
                qcolors.add(hexColor);
            }
        }
        Map<String,Object> res = new HashMap<>();
        res.put("brand", brand);
        res.put("zcm", zcm);
        res.put("qt", qt);
        res.put("bcolors", bcolors);
        res.put("zcolors", zcolors);
        res.put("qcolors", qcolors);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaMonthsInfo")
    public AjaxResult listAreaMonthsInfo(FugouVo vo){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String sql = "select distinct `month` as value,concat(`year`,'年',`month`,'月') as label from bigscreen.syf_product_target where type=1 and datatype=1 and `year`='"+year+"' and area='"+vo.getDpt()+"' ORDER BY `month` desc";
        List<Map<String,Object>> months = sysWeeklyZcmplanService.getChList(sql);
        Map<String,Object> res = new HashMap<>();
        res.put("months", months);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaMonths")
    public AjaxResult listAreaMonths(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String sql = "select distinct area as value,area as label from bigscreen.syf_product_target where type=1 and bigdpt='销售部' and area not in('销售部','何文丽','洋丽人')  and datatype=1 and `year`='"+year+"'";
        List<Map<String,Object>> areas = sysWeeklyZcmplanService.getChList(sql);
        String sqlm = "select distinct `month` as value,concat(`year`,'年',`month`,'月') as label from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=0 and `year`='"+year+"' ORDER BY `month` desc";
        List<Map<String,Object>> months = sysWeeklyZcmplanService.getChList(sqlm);
        Map<String,Object> res = new HashMap<>();
        res.put("areas", areas);
        res.put("months", months);
        res.put("area",areas.get(0).get("value"));
        res.put("month",months.get(0).get("value"));
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaYearData")
    public AjaxResult listAreaYearData(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String sql = "select distinct area as value,area as label from bigscreen.syf_product_target where type=0 and bigdpt='销售部' and area!='销售部' and datatype=0 and `year`='"+year+"'";
        List<Map<String,Object>> areas = sysWeeklyZcmplanService.getChList(sql);
        Map<String,Object> res = new HashMap<>();
        res.put("areas", areas);
        return AjaxResult.success(res);
    }

    @GetMapping("/listXsbMonths")
    public AjaxResult listXsbMonths(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String sql = "select distinct `month` as value,concat(`year`,'年',`month`,'月') as label from bigscreen.syf_product_target where type=1 and area='销售部' and datatype=0 and `year`='"+year+"' ORDER BY `month` desc";
        List<Map<String,Object>> months = sysWeeklyZcmplanService.getChList(sql);
        Map<String,Object> res = new HashMap<>();
        res.put("months", months);
        return AjaxResult.success(res);
    }

    @GetMapping("/listYearProduct")
    public AjaxResult listYearProduct(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String brandsql = "select name,ROUND(finished/10000,2) as finished,amount,orders from bigscreen.syf_product_target where type=0 and area='销售部' and datatype=0 and `year`='"+year+"'";
        String zcmsql = "select bigname,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and area='销售部' and datatype=1 and name='自产苗' and `year`='"+year+"' order by finished desc";
        String qtsql = "select bigname,ROUND(finished/10000,2) as finished,amount from bigscreen.syf_product_target where type=0 and area='销售部' and datatype=1 and name='其他' and `year`='"+year+"' order by finished desc";

        List<Map<String,Object>> brand = sysWeeklyZcmplanService.getChList(brandsql);
        List<Map<String,Object>> zcm = sysWeeklyZcmplanService.getChList(zcmsql);
        List<Map<String,Object>> qt = sysWeeklyZcmplanService.getChList(qtsql);
        Map<String,Object> res = new HashMap<>();
        res.put("brand", brand);
        res.put("zcm", zcm);
        res.put("qt", qt);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreaYearTarget")
    public AjaxResult listAreaYearTarget(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());

        String aysql = "SELECT `year` as `year`,area,ROUND(sum(target)/10000,2) as target,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(IFNULL(cctarget,0))/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where bigdpt='销售部' and area not in('洋丽人','何文丽') and type=1 and arch=1 and `year`='"+year+"' GROUP BY 1,2 order by htfinished desc";
        String aysqlsum = "select '-' as `year`,'合计' as area,ROUND(sum(target)/10000,2) as target,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(IFNULL(cctarget,0))/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where bigdpt='销售部' and type=1 and arch=1 and `year`='"+year+"' GROUP BY 1,2";

        List<Map<String,Object>> areayear = sysWeeklyZcmplanService.getChList(aysql);
        List<Map<String,Object>> areayearsum = sysWeeklyZcmplanService.getChList(aysqlsum);

        List<Map<String,Object>> areayeartable = new ArrayList<>();
        areayeartable.addAll(areayear);
        areayeartable.addAll(areayearsum);

        Map<String,Object> res = new HashMap<>();
        res.put("areayear", areayear);
        res.put("areayeartable", areayeartable);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAreasTarget")
    public AjaxResult listAreasTarget(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String jnsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='晋宁片区' and `year`='"+year+"' order by area asc;";
        String jnsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='晋宁片区' and type=1 and `year`='"+year+"'  GROUP BY 1";

        List<Map<String,Object>> jn = sysWeeklyZcmplanService.getChList(jnsql);
        List<Map<String,Object>> jns = sysWeeklyZcmplanService.getChList(jnsqls);


        String ylsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='宜良片区' and `year`='"+year+"' order by area asc;";
        String ylsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='宜良片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> yl = sysWeeklyZcmplanService.getChList(ylsql);
        List<Map<String,Object>> yls = sysWeeklyZcmplanService.getChList(ylsqls);

        String cgsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='呈贡片区' and `year`='"+year+"' order by area asc;";
        String cgsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='呈贡片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> cg = sysWeeklyZcmplanService.getChList(cgsql);
        List<Map<String,Object>> cgs = sysWeeklyZcmplanService.getChList(cgsqls);

        String smsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='嵩明片区' and `year`='"+year+"' order by area asc;";
        String smsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='嵩明片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> sm = sysWeeklyZcmplanService.getChList(smsql);
        List<Map<String,Object>> sms = sysWeeklyZcmplanService.getChList(smsqls);

        String ansql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='安宁片区' and `year`='"+year+"' order by area asc;";
        String ansqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='安宁片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> an = sysWeeklyZcmplanService.getChList(ansql);
        List<Map<String,Object>> ans = sysWeeklyZcmplanService.getChList(ansqls);

        String yxsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='玉溪片区' and `year`='"+year+"' order by area asc;";
        String yxsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='玉溪片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> yx = sysWeeklyZcmplanService.getChList(yxsql);
        List<Map<String,Object>> yxs = sysWeeklyZcmplanService.getChList(yxsqls);

        String slsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='石林片区' and `year`='"+year+"' order by area asc;";
        String slsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='石林片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> sl = sysWeeklyZcmplanService.getChList(slsql);
        List<Map<String,Object>> sls = sysWeeklyZcmplanService.getChList(slsqls);

        String cxsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='楚雄片区' and `year`='"+year+"' order by area asc;";
        String cxsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='楚雄片区' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> cx = sysWeeklyZcmplanService.getChList(cxsql);
        List<Map<String,Object>> cxs = sysWeeklyZcmplanService.getChList(cxsqls);

        String swsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where type=1 and arch=1 and area='省外' and `year`='"+year+"' order by area asc;";
        String swsqls = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,ROUND(sum(htfinished)/sum(cctarget)*100,2) as ccrate from bigscreen.syf_dept_area_target where area='省外' and type=1 and `year`='"+year+"'  GROUP BY 1";
        List<Map<String,Object>> sw = sysWeeklyZcmplanService.getChList(swsql);
        List<Map<String,Object>> sws = sysWeeklyZcmplanService.getChList(swsqls);

        List<Map<String,Object>> jnt = new ArrayList<>();
        jnt.addAll(jn);
        jnt.addAll(jns);
        List<Map<String,Object>> ylt = new ArrayList<>();
        ylt.addAll(yl);
        ylt.addAll(yls);
        List<Map<String,Object>> cgt = new ArrayList<>();
        cgt.addAll(cg);
        cgt.addAll(cgs);
        List<Map<String,Object>> smt = new ArrayList<>();
        smt.addAll(sm);
        smt.addAll(sms);
        List<Map<String,Object>> ant = new ArrayList<>();
        ant.addAll(an);
        ant.addAll(ans);
        List<Map<String,Object>> yxt = new ArrayList<>();
        yxt.addAll(yx);
        yxt.addAll(yxs);
        List<Map<String,Object>> slt = new ArrayList<>();
        slt.addAll(sl);
        slt.addAll(sls);
        List<Map<String,Object>> cxt = new ArrayList<>();
        cxt.addAll(cx);
        cxt.addAll(cxs);
        List<Map<String,Object>> swt = new ArrayList<>();
        swt.addAll(sw);
        swt.addAll(sws);
        Map<String,Object> res = new HashMap<>();
        res.put("jn", jn);
        res.put("yl", yl);
        res.put("cg", cg);
        res.put("sm", sm);
        res.put("an", an);
        res.put("yx", yx);
        res.put("sl", sl);
        res.put("cx", cx);
        res.put("sw", sw);
        res.put("jnt", jnt);
        res.put("ylt", ylt);
        res.put("cgt", cgt);
        res.put("smt", smt);
        res.put("ant", ant);
        res.put("yxt", yxt);
        res.put("slt", slt);
        res.put("cxt", cxt);
        res.put("swt", swt);
        return AjaxResult.success(res);
    }

    @GetMapping("/listComDeptTarget")
    public AjaxResult listComDeptTarget(){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
        String year = sdf.format(new Date());
        String csql = "select ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate from bigscreen.syf_company_target where year='"+year+"'";
        List<Map<String,Object>> company = sysWeeklyZcmplanService.getChList(csql);
        String dsql = "SELECT ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished_rate*100,2) as htrate from bigscreen.syf_dept_area_target where area='销售部' and type=0 and year='"+year+"'";
        List<Map<String,Object>> dept = sysWeeklyZcmplanService.getChList(dsql);

        String dmsql = "select `year`,concat(`year`,'年',`month`,'月') as `month`,ROUND(target/10000,2) as target,ROUND(finished/10000,2) as finished,ROUND(finish_rate*100,2) as rate,ROUND(htfinished/10000,2) as htfinished,ROUND(htfinished/target*100,2) as htrate,ROUND(cctarget/10000,2) as cctarget,ROUND(htfinished/cctarget*100,2) as ccrate from bigscreen.syf_dept_area_target where area='销售部' and type=1 and year='"+year+"' order by year asc,`month` asc";
        List<Map<String,Object>> deptmonth = sysWeeklyZcmplanService.getChList(dmsql);

        String dmsqlsum = "select '-' as `year`, '合计' as `month`,ROUND(sum(target)/10000,2) as target,'-' as finished,'-' as rate,ROUND(sum(htfinished)/10000,2) as htfinished,ROUND(sum(htfinished)/sum(target)*100,2) as htrate,ROUND(sum(cctarget)/10000,2) as cctarget,'-' as ccrate from bigscreen.syf_dept_area_target where area='销售部' and type=1 and year='"+year+"' GROUP BY 1";
        List<Map<String,Object>> deptmonthsum = sysWeeklyZcmplanService.getChList(dmsqlsum);
        List<Map<String,Object>> deptmonthtable = new ArrayList<>();
        deptmonthtable.addAll(deptmonth);
        deptmonthtable.addAll(deptmonthsum);
        Map<String,Object> res = new HashMap<>();
        res.put("company", company.get(0));
        res.put("dept", dept.get(0));
        res.put("deptmonth", deptmonth);
        res.put("deptmonthtable", deptmonthtable);
        return AjaxResult.success(res);
    }

    @GetMapping("/listCusDetailInfo")
    public AjaxResult listCusDetailInfo(FugouVo vo){
        String sql_info = "select distinct kh_id,kh_name,cphone,ms,zzppd,yzkh,dpt from tmp.kh_title where kh_id='"+vo.getKh_id()+"'";
        List<Map<String,Object>> extraInfo = sysWeeklyZcmplanService.getChList(sql_info);

        String sql_detail = "SELECT pname,ddstate," +
                "  sum( CASE WHEN period = 5 THEN orders ELSE 0 END ) AS y5order," +
                "  sum( CASE WHEN period = 4 THEN orders ELSE 0 END ) AS y4order," +
                "  sum( CASE WHEN period = 3 THEN orders ELSE 0 END ) AS y3order," +
                "  sum( CASE WHEN period = 2 THEN orders ELSE 0 END ) AS y2order," +
                "  sum( CASE WHEN period = 1 THEN orders ELSE 0 END ) AS y1order," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 5 THEN orders ELSE 0 END )/10000,2) AS y5avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 4 THEN orders ELSE 0 END )/10000,2) AS y4avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 3 THEN orders ELSE 0 END )/10000,2) AS y3avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 2 THEN orders ELSE 0 END )/10000,2) AS y2avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 1 THEN orders ELSE 0 END )/10000,2) AS y1avg" +
                " FROM tmp.kh_analys_orderDetail  WHERE 1=1 ";

        String sql_order = "SELECT brand, ddstate," +
                "  sum( CASE WHEN period = 5 THEN orders ELSE 0 END ) AS y5order," +
                "  sum( CASE WHEN period = 4 THEN orders ELSE 0 END ) AS y4order," +
                "  sum( CASE WHEN period = 3 THEN orders ELSE 0 END ) AS y3order," +
                "  sum( CASE WHEN period = 2 THEN orders ELSE 0 END ) AS y2order," +
                "  sum( CASE WHEN period = 1 THEN orders ELSE 0 END ) AS y1order," +
                "  ROUND(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),2) AS y5money," +
                "  ROUND(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),2) AS y4money," +
                "  ROUND(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),2) AS y3money," +
                "  ROUND(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),2) AS y2money," +
                "  ROUND(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),2) AS y1money," +
                "  sum(orders) as totalorders," +
                "  ROUND(sum(moneys),2) as totalmoneys" +
                " FROM tmp.kh_analys_orderDetail WHERE 1=1 and ddstate='z' ";

        String sql_refund = "SELECT brand, ddstate," +
                "  sum( CASE WHEN period = 5 THEN orders ELSE 0 END ) AS y5order," +
                "  sum( CASE WHEN period = 4 THEN orders ELSE 0 END ) AS y4order," +
                "  sum( CASE WHEN period = 3 THEN orders ELSE 0 END ) AS y3order," +
                "  sum( CASE WHEN period = 2 THEN orders ELSE 0 END ) AS y2order," +
                "  sum( CASE WHEN period = 1 THEN orders ELSE 0 END ) AS y1order," +
                "  ROUND(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),2) AS y5money," +
                "  ROUND(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),2) AS y4money," +
                "  ROUND(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),2) AS y3money," +
                "  ROUND(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),2) AS y2money," +
                "  ROUND(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),2) AS y1money," +
                "  sum(orders) as totalorders," +
                "  ROUND(sum(moneys),2) as totalmoneys" +
                " FROM tmp.kh_analys_orderDetail WHERE 1=1 and ddstate='t' ";

        sql_detail += " and kh_id='"+vo.getKh_id()+"'";
        sql_order += " and kh_id='"+vo.getKh_id()+"'";
        sql_refund += " and kh_id='"+vo.getKh_id()+"'";

        String sql_rank = "select distinct life_cycle as sdmc,ROUND(vip,2) as vipn,rank_vip as vipcn from tmp.kh_title where kh_id='"+vo.getKh_id()+"' order by life_cycle desc";

        if(!vo.getBrand().equalsIgnoreCase("all")){
            sql_detail += " and brand='"+vo.getBrand()+"'";
            sql_order += " and brand='"+vo.getBrand()+"'";
            sql_refund += " and brand='"+vo.getBrand()+"'";
        }

        if(!vo.getDdstate().equalsIgnoreCase("all")){
            sql_detail += " and ddstate='"+vo.getDdstate()+"'";
            sql_order += " and ddstate='"+vo.getDdstate()+"'";
            sql_refund += " and ddstate='"+vo.getDdstate()+"'";
        }
        sql_detail += " GROUP BY 1,2";
        sql_order += " GROUP BY 1,2";
        sql_refund += " GROUP BY 1,2";

        List<Map<String,Object>> detailList = sysWeeklyZcmplanService.getChList(sql_detail);
        List<Map<String,Object>> orderSummary = sysWeeklyZcmplanService.getChList(sql_order);
        List<Map<String,Object>> refundSummary = sysWeeklyZcmplanService.getChList(sql_refund);
        List<Map<String,Object>> rankSummary = sysWeeklyZcmplanService.getChList(sql_rank);
        Map<String,Object> res = new HashMap<>();
        res.put("extraInfo",extraInfo.get(0));
        res.put("detailInfo",detailList);
        res.put("orderSummary",orderSummary);
        res.put("refundSummary",refundSummary);
        res.put("rankSummary",rankSummary);
        return AjaxResult.success(res);
    }

    @GetMapping("/listKhAnlyseDetailData")
    public AjaxResult listKhAnlyseDetailData(FugouVo vo){
        String sql_sum = "select * from tmp.kh_analys_orderDetail where 1=1 ";
        String sql = "select * from tmp.kh_analys_orderDetail where 1=1 ";

        if(!vo.getBigdpt().equalsIgnoreCase("all")){
            if(vo.getBigdpt().equalsIgnoreCase("empty")){
                sql_sum += " and bigdpt is null ";
                sql += " and bigdpt is null ";
            }else{
                sql_sum += " and bigdpt='"+vo.getBigdpt()+"'";
                sql += " and bigdpt='"+vo.getBigdpt()+"'";
            }
        }

        if(!vo.getDpt().equalsIgnoreCase("all")){
            if(vo.getDpt().equalsIgnoreCase("empty")){
                sql_sum += " and dpt is null ";
                sql += " and dpt is null ";
            }else {
                sql_sum += " and dpt='"+vo.getDpt()+"'";
                sql += " and dpt='"+vo.getDpt()+"'";
            }
        }
        if(!vo.getDptcls().equalsIgnoreCase("all")){
            sql_sum += " and dptcls='"+vo.getDptcls()+"'";
            sql += " and dptcls='"+vo.getDptcls()+"'";
        }
        if(!vo.getBrand().equalsIgnoreCase("all")){
            if (vo.getBrand().equalsIgnoreCase("empty")) {
                sql_sum += " and brand is null ";
                sql += " and brand is null ";
            }else{
                sql_sum += " and brand='"+vo.getBrand()+"'";
                sql += " and brand='"+vo.getBrand()+"'";
            }
        }
        if(vo.getKh_name()!=null&&!vo.getKh_name().toString().equalsIgnoreCase("null")){
            if(vo.getKh_name().replaceAll(" ","").length()>0){
                sql_sum += " and kh_name like '%"+vo.getKh_name()+"%'";
                sql += " and kh_name like '%"+vo.getKh_name()+"%'";
            }
        }
        if(!vo.getDdstate().equalsIgnoreCase("all")){
            sql_sum += " and ddstate='"+vo.getDdstate()+"'";
            sql += " and ddstate='"+vo.getDdstate()+"'";
        }
        String sum = "select count(*) as totalPage from (SELECT kh_id," +
                "  sum( CASE WHEN period = 5 THEN orders ELSE 0 END ) AS y5order," +
                "  sum( CASE WHEN period = 4 THEN orders ELSE 0 END ) AS y4order," +
                "  sum( CASE WHEN period = 3 THEN orders ELSE 0 END ) AS y3order," +
                "  sum( CASE WHEN period = 2 THEN orders ELSE 0 END ) AS y2order," +
                "  sum( CASE WHEN period = 1 THEN orders ELSE 0 END ) AS y1order," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 5 THEN orders ELSE 0 END )/10000,2) AS y5avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 4 THEN orders ELSE 0 END )/10000,2) AS y4avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 3 THEN orders ELSE 0 END )/10000,2) AS y3avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 2 THEN orders ELSE 0 END )/10000,2) AS y2avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 1 THEN orders ELSE 0 END )/10000,2) AS y1avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),0)/10000,2) AS y5money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),0)/10000,2) AS y4money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),0)/10000,2) AS y3money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),0)/10000,2) AS y2money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),0)/10000,2) AS y1money " +
                " FROM ("+sql_sum+") b GROUP BY 1) a";
        List<Map<String,Object>> countList = sysWeeklyZcmplanService.getChList(sum);
        int totalPage = 0;
        int totalRecord = 0;
        if(countList.size()>0){
            int total = Integer.parseInt(countList.get(0).get("totalPage").toString());
            totalRecord = total;
            int quotient  = total / vo.getPageSize();  // 向零取整的商
            int remainder = total % vo.getPageSize();
            if(remainder>0){
                totalPage = quotient+1;
            }else{
                totalPage = quotient;
            }
        }

        String sql_list = "select * from (SELECT kh_id," +
                "  sum( CASE WHEN period = 5 THEN orders ELSE 0 END ) AS y5order," +
                "  sum( CASE WHEN period = 4 THEN orders ELSE 0 END ) AS y4order," +
                "  sum( CASE WHEN period = 3 THEN orders ELSE 0 END ) AS y3order," +
                "  sum( CASE WHEN period = 2 THEN orders ELSE 0 END ) AS y2order," +
                "  sum( CASE WHEN period = 1 THEN orders ELSE 0 END ) AS y1order," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 5 THEN orders ELSE 0 END )/10000,2) AS y5avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 4 THEN orders ELSE 0 END )/10000,2) AS y4avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 3 THEN orders ELSE 0 END )/10000,2) AS y3avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 2 THEN orders ELSE 0 END )/10000,2) AS y2avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),0)/sum( CASE WHEN period = 1 THEN orders ELSE 0 END )/10000,2) AS y1avg," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 5 THEN moneys ELSE 0 END ),0)/10000,2) AS y5money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 4 THEN moneys ELSE 0 END ),0)/10000,2) AS y4money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 3 THEN moneys ELSE 0 END ),0)/10000,2) AS y3money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 2 THEN moneys ELSE 0 END ),0)/10000,2) AS y2money," +
                "  ROUND(IFNULL(sum( CASE WHEN period = 1 THEN moneys ELSE 0 END ),0)/10000,2) AS y1money " +
                " FROM ("+sql+") b GROUP BY 1) a "+" limit "+(vo.getPageNo()-1)*vo.getPageSize()+","+vo.getPageSize();
        List<Map<String,Object>> list = sysWeeklyZcmplanService.getChList(sql_list);
        if(list.size()>0){
            for (Map<String,Object> map : list) {
                String kh_id = map.get("kh_id").toString();
                String sub_sql = "select * from tmp.kh_analys_orderDetail where kh_id='"+kh_id+"'";
                List<Map<String,Object>> subList = sysWeeklyZcmplanService.getChList(sub_sql);
                if(subList.size()>0){
                    map.put("kh_name",subList.get(0).get("kh_name"));
                    map.put("n_o_cus",subList.get(0).get("n_o_cus"));
                    map.put("qr",subList.get(0).get("qr"));
                    map.put("xr",subList.get(0).get("xr"));
                }
            }
        }
        Map<String,Object> res = new HashMap<>();
        res.put("list",list);
        res.put("totalPage",totalPage);
        res.put("pageNo",vo.getPageNo());
        res.put("pageSize",vo.getPageSize());
        res.put("totalRecord",totalRecord);
        return AjaxResult.success(res);
    }

    @GetMapping("/listFgBrandData")
    public AjaxResult listFgBrandData(FugouVo vo){

        String sql = "SELECT period AS sdbs,life_cycle AS sdmc,brand,ROUND(avg( cus_count )) AS khjs,sum(IFNUll( fgrs, 0 )) AS fgrs,sum(IFNUll( xdrs, 0 )) AS xdrs, ROUND( sum( IFNUll( fgrs, 0 ))/ sum( IFNUll( xdrs, 0 ))* 100, 2 ) AS fglv,ROUND( sum( IFNUll( xdrs, 0 ))/ ROUND(avg( cus_count ))* 100, 2 ) AS xdlv FROM tmp.kh_analys_xdfg_brand_rate WHERE 1=1 ";
        if(!vo.getBigdpt().equalsIgnoreCase("all")){
            sql += " and bigdpt='"+vo.getBigdpt()+"'";
        }
        if(!vo.getDpt().equalsIgnoreCase("all")){
            sql += " and dpt='"+vo.getDpt()+"'";
        }
        if(!vo.getDptcls().equalsIgnoreCase("all")){
            sql += " and dptcls='"+vo.getDptcls()+"'";
        }
        if(vo.getBrand().equalsIgnoreCase("empty")){
            sql += " and brand is null";
        }else{
            sql += " and brand='"+vo.getBrand()+"'";
        }
        sql += "GROUP BY 1,2,3 order by sdbs asc";
        List<Map<String,Object>> res = sysWeeklyZcmplanService.getChList(sql);
        return AjaxResult.success(res);
    }

    @GetMapping("/listFgData")
    public AjaxResult listFgData(FugouVo vo){
        String sql = "select period as sdbs,life_cycle as sdmc,sum(IFNUll(cus_count,0)) as khjs,sum(IFNUll(fgrs,0)) as fgrs,sum(IFNUll(xdrs,0)) as xdrs,ROUND(sum(IFNUll(fgrs,0))/sum(IFNUll(xdrs,0))*100,2) as fglv,ROUND(sum(IFNUll(xdrs,0))/sum(IFNUll(cus_count,0))*100,2) as xdlv from tmp.kh_analys_xdfg_rate where 1=1 ";
        if(!vo.getBigdpt().equalsIgnoreCase("all")){
            sql += " and bigdpt='"+vo.getBigdpt()+"'";
        }
        if(!vo.getDpt().equalsIgnoreCase("all")){
            sql += " and dpt='"+vo.getDpt()+"'";
        }
        if(!vo.getDptcls().equalsIgnoreCase("all")){
            sql += " and dptcls='"+vo.getDptcls()+"'";
        }
        sql += " GROUP BY 1,2 order by sdbs asc";
        List<Map<String,Object>> res = sysWeeklyZcmplanService.getChList(sql);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAnlyseDetailInit")
    public AjaxResult listAnlyseDetailInit(){
        SysUser user = SecurityUtils.getLoginUser().getUser();
        boolean queryFlag = true;
        String dptName = "";
        if(user!=null){
            List<SysRole> roles = user.getRoles();
            if(roles!=null&&roles.size()>0){
                for(SysRole role:roles){
                    if(role.getRoleKey().equalsIgnoreCase("xsbfzr")){
                        queryFlag = false;
                        break;
                    }else if(role.getRoleKey().equalsIgnoreCase("admin")){
                        queryFlag = false;
                        break;
                    }else{
                        if(role.getRoleKey().startsWith("xsry")){
                            dptName = role.getRoleName();
                        }
                        queryFlag = true;
                    }
                }
            }
        }
        String bigdpt = "";
        String dpt = "";
        String brand = "all";
        List<Map<String,Object>> bigdpts = new ArrayList<>();
        Map<String,Object> fbigdpt = new HashMap<>();
        fbigdpt.put("value","all");
        fbigdpt.put("label","(全部)");
        bigdpts.add(fbigdpt);
        List<Map<String,Object>> dpts = new ArrayList<>();
        Map<String,Object> fdpt = new HashMap<>();
        fdpt.put("value","all");
        fdpt.put("label","(全部)");
        dpts.add(fdpt);
        List<Map<String,Object>> brands = new ArrayList<>();
        Map<String,Object> fbrands= new HashMap<>();
        fbrands.put("value","all");
        fbrands.put("label","(全部)");
        brands.add(fbrands);
        String sql_bd = "select distinct bigdpt as `value`,bigdpt as label from tmp.kh_analys_orderDetail where bigdpt is not null";
        List<Map<String,Object>> sbigdpt = sysWeeklyZcmplanService.getChList(sql_bd);
        Map<String,Object> nbigdpt = new HashMap<>();
        nbigdpt.put("value","empty");
        nbigdpt.put("label","Null");
        bigdpts.addAll(sbigdpt);
        bigdpts.add(nbigdpt);
        String sql_dpt = "";
        String sql_brand = "";
        if(queryFlag){
            bigdpt = "销售部";
            dpt = dptName;
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_orderDetail where bigdpt='"+bigdpt+"'";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_orderDetail where bigdpt='"+bigdpt+"' and dpt='"+dpt+"' and brand is not null";
        }else{
            bigdpt = "all";
            dpt = "all";
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_orderDetail where dpt is not null";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_xdfg_brand_rate where brand is not null";
        }
        List<Map<String,Object>> sbrands = sysWeeklyZcmplanService.getChList(sql_brand);
        brands.addAll(sbrands);
        Map<String,Object> nbrand = new HashMap<>();
        nbrand.put("value","empty");
        nbrand.put("label","Null");
        brands.add(nbrand);
        List<Map<String,Object>> sdpts = sysWeeklyZcmplanService.getChList(sql_dpt);
        dpts.addAll(sdpts);
        Map<String,Object> ndpt = new HashMap<>();
        ndpt.put("value","empty");
        ndpt.put("label","Null");
        dpts.add(ndpt);
        String dptcls = "all";
        List<Map<String,Object>> dptclss = new ArrayList<>();
        Map<String,Object> dptcls1 = new HashMap<>();
        dptcls1.put("value","all");
        dptcls1.put("label","(全部)");
        Map<String,Object> dptcls2 = new HashMap<>();
        dptcls2.put("value","正常");
        dptcls2.put("label","正常");
        Map<String,Object> dptcls3 = new HashMap<>();
        dptcls3.put("value","公海");
        dptcls3.put("label","公海");
        dptclss.add(dptcls1);
        dptclss.add(dptcls2);
        dptclss.add(dptcls3);

        String ddstate = "all";
        List<Map<String,Object>> ddstates = new ArrayList<>();
        Map<String,Object> ddstate1 = new HashMap<>();
        ddstate1.put("value","all");
        ddstate1.put("label","(全部)");
        Map<String,Object> ddstate2 = new HashMap<>();
        ddstate2.put("value","z");
        ddstate2.put("label","正常");
        Map<String,Object> ddstate3 = new HashMap<>();
        ddstate3.put("value","t");
        ddstate3.put("label","退单");
        ddstates.add(ddstate1);
        ddstates.add(ddstate2);
        ddstates.add(ddstate3);
        Map<String,Object> res = new HashMap<>();
        res.put("queryFlag",queryFlag);

        res.put("bigdpt",bigdpt);
        res.put("dpt",dpt);
        res.put("brand",brand);
        res.put("dptcls",dptcls);
        res.put("kh_name","");
        res.put("ddstate",ddstate);

        res.put("bigdpts",bigdpts);
        res.put("dpts",dpts);
        res.put("brands",brands);
        res.put("dptclss",dptclss);
        res.put("ddstates",ddstates);
        return AjaxResult.success(res);
    }

    @GetMapping("/listAnlyseBrandInit")
    public AjaxResult listAnlyseBrandInit(){
        SysUser user = SecurityUtils.getLoginUser().getUser();
        boolean queryFlag = true;
        String dptName = "";
        if(user!=null){
            List<SysRole> roles = user.getRoles();
            if(roles!=null&&roles.size()>0){
                for(SysRole role:roles){
                    if(role.getRoleKey().equalsIgnoreCase("xsbfzr")){
                        queryFlag = false;
                        break;
                    }else if(role.getRoleKey().equalsIgnoreCase("admin")){
                        queryFlag = false;
                        break;
                    }else{
                        if(role.getRoleKey().startsWith("xsry")){
                            dptName = role.getRoleName();
                        }
                        queryFlag = true;
                    }
                }
            }
        }
        String bigdpt = "";
        String dpt = "";
        String brand = "自产苗";
        List<Map<String,Object>> bigdpts = new ArrayList<>();
        Map<String,Object> fbigdpt = new HashMap<>();
        fbigdpt.put("value","all");
        fbigdpt.put("label","(全部)");
        bigdpts.add(fbigdpt);
        List<Map<String,Object>> dpts = new ArrayList<>();
        Map<String,Object> fdpt = new HashMap<>();
        fdpt.put("value","all");
        fdpt.put("label","(全部)");
        dpts.add(fdpt);
        List<Map<String,Object>> brands = new ArrayList<>();
        String sql_bd = "select distinct bigdpt as `value`,bigdpt as label from tmp.kh_analys_xdfg_rate";
        List<Map<String,Object>> sbigdpt = sysWeeklyZcmplanService.getChList(sql_bd);
        bigdpts.addAll(sbigdpt);
        String sql_dpt = "";
        String sql_brand = "";
        if(queryFlag){
            bigdpt = "销售部";
            dpt = dptName;
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_xdfg_rate where bigdpt='"+bigdpt+"'";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_xdfg_brand_rate where bigdpt='"+bigdpt+"' and dpt='"+dpt+"' and brand is not null";
        }else{
            bigdpt = "all";
            dpt = "all";
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_xdfg_rate";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_xdfg_brand_rate where brand is not null";
        }
        List<Map<String,Object>> sbrands = sysWeeklyZcmplanService.getChList(sql_brand);
        brands.addAll(sbrands);
        Map<String,Object> nbrand = new HashMap<>();
        nbrand.put("value","empty");
        nbrand.put("label","Null");
        brands.add(nbrand);
        List<Map<String,Object>> sdpts = sysWeeklyZcmplanService.getChList(sql_dpt);
        dpts.addAll(sdpts);
        String dptcls = "all";
        List<Map<String,Object>> dptclss = new ArrayList<>();
        Map<String,Object> dptcls1 = new HashMap<>();
        dptcls1.put("value","all");
        dptcls1.put("label","(全部)");
        Map<String,Object> dptcls2 = new HashMap<>();
        dptcls2.put("value","正常");
        dptcls2.put("label","正常");
        Map<String,Object> dptcls3 = new HashMap<>();
        dptcls3.put("value","公海");
        dptcls3.put("label","公海");
        dptclss.add(dptcls1);
        dptclss.add(dptcls2);
        dptclss.add(dptcls3);

        String ddstate = "all";
        List<Map<String,Object>> ddstates = new ArrayList<>();
        Map<String,Object> ddstate1 = new HashMap<>();
        ddstate1.put("value","all");
        ddstate1.put("label","(全部)");
        Map<String,Object> ddstate2 = new HashMap<>();
        ddstate2.put("value","z");
        ddstate2.put("label","正常");
        Map<String,Object> ddstate3 = new HashMap<>();
        ddstate3.put("value","t");
        ddstate3.put("label","退单");
        ddstates.add(ddstate1);
        ddstates.add(ddstate2);
        ddstates.add(ddstate3);
        Map<String,Object> res = new HashMap<>();
        res.put("queryFlag",queryFlag);

        res.put("bigdpt",bigdpt);
        res.put("dpt",dpt);
        res.put("brand",brand);
        res.put("dptcls",dptcls);
        res.put("ddstate",ddstate);

        res.put("bigdpts",bigdpts);
        res.put("dpts",dpts);
        res.put("brands",brands);
        res.put("dptclss",dptclss);
        res.put("ddstates",ddstates);
        return AjaxResult.success(res);
    }

    @GetMapping("/listCusAnyliseInit")
    public AjaxResult listCusAnyliseInit(){
        SysUser user = SecurityUtils.getLoginUser().getUser();
        boolean queryFlag = true;
        String dptName = "";
        if(user!=null){
            List<SysRole> roles = user.getRoles();
            if(roles!=null&&roles.size()>0){
                for(SysRole role:roles){
                    if(role.getRoleKey().equalsIgnoreCase("xsbfzr")){
                        queryFlag = false;
                        break;
                    }else if(role.getRoleKey().equalsIgnoreCase("admin")){
                        queryFlag = false;
                        break;
                    }else{
                        if(role.getRoleKey().startsWith("xsry")){
                            dptName = role.getRoleName();
                        }
                        queryFlag = true;
                    }
                }
            }
        }
        String bigdpt = "";
        String dpt = "";
        String brand = "all";
        List<Map<String,Object>> bigdpts = new ArrayList<>();
        Map<String,Object> fbigdpt = new HashMap<>();
        fbigdpt.put("value","all");
        fbigdpt.put("label","(全部)");
        bigdpts.add(fbigdpt);
        List<Map<String,Object>> dpts = new ArrayList<>();
        Map<String,Object> fdpt = new HashMap<>();
        fdpt.put("value","all");
        fdpt.put("label","(全部)");
        dpts.add(fdpt);
        List<Map<String,Object>> brands = new ArrayList<>();
        Map<String,Object> fbrand = new HashMap<>();
        fbrand.put("value","all");
        fbrand.put("label","(全部)");
        brands.add(fbrand);
        String sql_bd = "select distinct bigdpt as `value`,bigdpt as label from tmp.kh_analys_xdfg_rate";
        List<Map<String,Object>> sbigdpt = sysWeeklyZcmplanService.getChList(sql_bd);
        bigdpts.addAll(sbigdpt);
        String sql_dpt = "";
        String sql_brand = "";
        if(queryFlag){
            bigdpt = "销售部";
            dpt = dptName;
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_xdfg_rate where bigdpt='"+bigdpt+"'";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_xdfg_brand_rate where bigdpt='"+bigdpt+"' and dpt='"+dpt+"' and brand is not null";
        }else{
            bigdpt = "all";
            dpt = "all";
            sql_dpt = "select distinct dpt as `value`,dpt as label from tmp.kh_analys_xdfg_rate";
            sql_brand = "select distinct brand as `value`,brand as label from tmp.kh_analys_xdfg_brand_rate where brand is not null";
        }
        List<Map<String,Object>> sbrands = sysWeeklyZcmplanService.getChList(sql_brand);
        brands.addAll(sbrands);
        Map<String,Object> nbrand = new HashMap<>();
        nbrand.put("value","empty");
        nbrand.put("label","Null");
        brands.add(nbrand);
        List<Map<String,Object>> sdpts = sysWeeklyZcmplanService.getChList(sql_dpt);
        dpts.addAll(sdpts);
        String dptcls = "all";
        List<Map<String,Object>> dptclss = new ArrayList<>();
        Map<String,Object> dptcls1 = new HashMap<>();
        dptcls1.put("value","all");
        dptcls1.put("label","(全部)");
        Map<String,Object> dptcls2 = new HashMap<>();
        dptcls2.put("value","正常");
        dptcls2.put("label","正常");
        Map<String,Object> dptcls3 = new HashMap<>();
        dptcls3.put("value","公海");
        dptcls3.put("label","公海");
        dptclss.add(dptcls1);
        dptclss.add(dptcls2);
        dptclss.add(dptcls3);

        String ddstate = "all";
        List<Map<String,Object>> ddstates = new ArrayList<>();
        Map<String,Object> ddstate1 = new HashMap<>();
        ddstate1.put("value","all");
        ddstate1.put("label","(全部)");
        Map<String,Object> ddstate2 = new HashMap<>();
        ddstate2.put("value","z");
        ddstate2.put("label","正常");
        Map<String,Object> ddstate3 = new HashMap<>();
        ddstate3.put("value","t");
        ddstate3.put("label","退单");
        ddstates.add(ddstate1);
        ddstates.add(ddstate2);
        ddstates.add(ddstate3);
        Map<String,Object> res = new HashMap<>();
        res.put("queryFlag",queryFlag);

        res.put("bigdpt",bigdpt);
        res.put("dpt",dpt);
        res.put("brand",brand);
        res.put("dptcls",dptcls);
        res.put("ddstate",ddstate);

        res.put("bigdpts",bigdpts);
        res.put("dpts",dpts);
        res.put("brands",brands);
        res.put("dptclss",dptclss);
        res.put("ddstates",ddstates);
        return AjaxResult.success(res);
    }

    @GetMapping("/listChData")
    public AjaxResult listChData(SysWeeklyZcmplan plan){
        String sql = "";
        sql += "select bigname," +
                "sum( IFNULL(tge_money, 0) ) AS target_sum," +
                "sum( IFNULL(ht_money, 0) ) AS achieve_sum," +
                "sum( CASE WHEN dpt = '嵩明片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sm," +
                "sum( CASE WHEN dpt = '嵩明片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sm," +
                "sum( CASE WHEN dpt = '宜良片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_yl," +
                "sum( CASE WHEN dpt = '宜良片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_yl," +
                "sum( CASE WHEN dpt = '呈贡片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_cg," +
                "sum( CASE WHEN dpt = '呈贡片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_cg," +
                "sum( CASE WHEN dpt = '楚雄片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_cx," +
                "sum( CASE WHEN dpt = '楚雄片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_cx," +
                "sum( CASE WHEN dpt = '晋宁片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_jn," +
                "sum( CASE WHEN dpt = '晋宁片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_jn," +
                "sum( CASE WHEN dpt = '玉溪片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_yx," +
                "sum( CASE WHEN dpt = '玉溪片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_yx," +
                "sum( CASE WHEN dpt = '省外' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sw," +
                "sum( CASE WHEN dpt = '省外' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sw," +
                "sum( CASE WHEN dpt = '石林片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sl," +
                "sum( CASE WHEN dpt = '石林片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sl," +
                "sum( CASE WHEN dpt = '安宁片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_an," +
                "sum( CASE WHEN dpt = '安宁片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_an ";
        sql += "from weekly_zcmplan where bigname not in('其他','洋桔梗','洋牡丹') and wk='"+plan.getWk()+"' and wk_year like '%"+plan.getWkYear()+"%' GROUP BY 1 order by target_sm desc";
        List<Map<String,Object>> chList = sysWeeklyZcmplanService.getChList(sql);
        List<Map<String,Object>> res = new ArrayList<>();
        if(chList.size()>0){
            for(int i=0;i<chList.size();i++){
                List<Map<String,Object>> ymd = new ArrayList<>();
                ymd.add(chList.get(i));
                Map<String,Object> map = converDataToMap(ymd,chList.get(i).get("bigname").toString());
                res.add(map);
            }
        }

        String sqlsum = "";
        sqlsum += "select sum(a.target_sum) as target_sum," +
                "               sum(a.achieve_sum) as achieve_sum," +
                "               sum(a.target_sm) as target_sm," +
                "               sum(a.achieve_sm) as achieve_sm," +
                "               sum(a.target_yl) as target_yl," +
                "               sum(a.achieve_yl) as achieve_yl," +
                "               sum(a.target_cg) as target_cg," +
                "               sum(a.achieve_cg) as achieve_cg," +
                "               sum(a.target_cx) as target_cx," +
                "               sum(a.achieve_cx) as achieve_cx," +
                "               sum(a.target_jn) as target_jn," +
                "               sum(a.achieve_jn) as achieve_jn," +
                "               sum(a.target_yx) as target_yx," +
                "               sum(a.achieve_yx) as achieve_yx," +
                "               sum(a.target_sw) as target_sw," +
                "               sum(a.achieve_sw) as achieve_sw," +
                "               sum(a.target_sl) as target_sl," +
                "               sum(a.achieve_sl) as achieve_sl," +
                "               sum(a.target_an) as target_an," +
                "               sum(a.achieve_an) as achieve_an" +
                "        from (SELECT" +
                "                  bigname," +
                "                  sum( IFNULL(tge_money, 0) ) AS target_sum," +
                "                  sum( IFNULL(ht_money, 0) ) AS achieve_sum," +
                "                  sum( CASE WHEN dpt = '嵩明片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sm," +
                "                  sum( CASE WHEN dpt = '嵩明片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sm," +
                "                  sum( CASE WHEN dpt = '宜良片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_yl," +
                "                  sum( CASE WHEN dpt = '宜良片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_yl," +
                "                  sum( CASE WHEN dpt = '呈贡片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_cg," +
                "                  sum( CASE WHEN dpt = '呈贡片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_cg," +
                "                  sum( CASE WHEN dpt = '楚雄片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_cx," +
                "                  sum( CASE WHEN dpt = '楚雄片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_cx," +
                "                  sum( CASE WHEN dpt = '晋宁片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_jn," +
                "                  sum( CASE WHEN dpt = '晋宁片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_jn," +
                "                  sum( CASE WHEN dpt = '玉溪片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_yx," +
                "                  sum( CASE WHEN dpt = '玉溪片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_yx," +
                "                  sum( CASE WHEN dpt = '省外' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sw," +
                "                  sum( CASE WHEN dpt = '省外' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sw," +
                "                  sum( CASE WHEN dpt = '石林片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_sl," +
                "                  sum( CASE WHEN dpt = '石林片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_sl," +
                "                  sum( CASE WHEN dpt = '安宁片区' THEN IFNULL(tge_money,0) ELSE 0 END ) AS target_an," +
                "                  sum( CASE WHEN dpt = '安宁片区' THEN IFNULL(ht_money,0) ELSE 0 END ) AS achieve_an" +
                "              from weekly_zcmplan where bigname not in('其他','洋桔梗','洋牡丹') and wk='"+plan.getWk()+"' and wk_year like '%"+plan.getWkYear()+"%' GROUP BY 1 order by target_sm desc) a";

//        List<Map<String,Object>> summary = sysWeeklyZcmplanService.selectChSummary(plan.getWkYear(),plan.getWk());
        List<Map<String,Object>> summary = sysWeeklyZcmplanService.selectCaohuaSummary(sqlsum);
        if(summary.get(0)==null){
            summary.remove(0);
        }
        Map<String,Object> summaryMap = converDataToMap(summary,"总和");
        res.add(summaryMap);

        String totalsql = "";
        totalsql += "select " +
                "CAST(sum(tge_money) AS DECIMAL(10, 2)) as target," +
                "CAST(sum(ht_money) AS DECIMAL(10, 2)) as finish," +
                "CAST(sum(ht_money)/sum(tge_money)*100 AS DECIMAL(10, 2)) as rate from weekly_zcmplan where wk='"+plan.getWk()+"'";
//                "CAST(sum(ht_money)/sum(tge_money)*100 AS DECIMAL(10, 2)) as rate from weekly_zcmplan where wk='"+plan.getWk()+"' and bigname not in('洋桔梗','洋牡丹')"; //不显示洋桔梗和洋牡丹的
        List<Map<String,Object>> totalSummary = sysWeeklyZcmplanService.selectCaohuaTotalSummary(totalsql);

        Map<String,Object> result = new HashMap<>();
        result.put("list",res);
        result.put("summary",summaryMap);
        result.put("total",totalSummary);
        return AjaxResult.success(result);
    }

    @GetMapping("/listRefund")
    public AjaxResult listRefund(SysWeeklyZcmplan plan){
        List<Map<String,Object>> result = new ArrayList<>();
        BigDecimal sums = BigDecimal.ZERO;
        String sql = "select htcode as orderNo,money as orderFee,qty as qty,brand,bigname,xlname,xsname,dpt from tmp.yy_xsht_endorder_final where wk_year is not null and wk_year like '%"+plan.getWkYear()+"%' and wk_year like '%"+plan.getWk().replaceAll("w","")+"wk%' and bigdpt='销售部'";
        List<Map<String,Object>> refunds = sysWeeklyZcmplanService.selectCaohuaSummary(sql);
        if(refunds.size()>0){
            for (Map<String,Object> map : refunds) {
                if(map.get("orderFee")!=null){
                    BigDecimal refundFee = new BigDecimal(map.get("orderFee").toString()).divide(new BigDecimal("10000"), 2, BigDecimal.ROUND_HALF_UP);
                    sums = sums.add(refundFee);
                    map.put("orderFee",refundFee);
                }
            }
            result.addAll(refunds);
        }
        Map<String,Object> sumInfo = new HashMap<>();
        sumInfo.put("orderNo","总和");
        sumInfo.put("orderFee",sums.setScale(2,BigDecimal.ROUND_HALF_UP));
        sumInfo.put("qty","-");
        sumInfo.put("brand","-");
        sumInfo.put("bigname","-");
        sumInfo.put("xlname","-");
        sumInfo.put("xsname","-");
        sumInfo.put("dpt","-");
        result.add(sumInfo);
        Map<String,Object> summaryMap = new HashMap<>();
        summaryMap.put("sum",sums);
        Map<String,Object> res = new HashMap<>();
        res.put("list",result);
        res.put("summary",summaryMap);
        return AjaxResult.success(res);
    }

    @GetMapping("/listQtData")
    public AjaxResult listQtData(SysWeeklyZcmplan plan){
        String[] brand = {"其他"};
        List<Map<String,Object>> res = new ArrayList<>();
        for(int i=0;i<brand.length;i++){
            List<Map<String,Object>> ymd = sysWeeklyZcmplanService.selectYmdList(plan.getWkYear(),plan.getWk(),brand[i]);
            Map<String,Object> map = converDataToMap(ymd,brand[i]);
            res.add(map);
        }
        List<Map<String,Object>> summary = sysWeeklyZcmplanService.selectQtSummary(plan.getWkYear(),plan.getWk());
        if(summary.get(0)==null){
            summary.remove(0);
        }
        Map<String,Object> summaryMap = converDataToMap(summary,"总和");
        res.add(summaryMap);
        Map<String,Object> result = new HashMap<>();
        result.put("list",res);
        result.put("summary",summaryMap);
        return AjaxResult.success(result);
    }

    @GetMapping("/listQlData")
    public AjaxResult listQlData(SysWeeklyZcmplan plan){
        String[] brand = {"洋牡丹","洋桔梗"};
        List<Map<String,Object>> res = new ArrayList<>();
        for(int i=0;i<brand.length;i++){
            List<Map<String,Object>> ymd = sysWeeklyZcmplanService.selectYmdList(plan.getWkYear(),plan.getWk(),brand[i]);
            Map<String,Object> map = converDataToMap(ymd,brand[i]);
            res.add(map);
        }
        List<Map<String,Object>> summary = sysWeeklyZcmplanService.selectQlSummary(plan.getWkYear(),plan.getWk());
        if(summary.get(0)==null){
            summary.remove(0);
        }
        Map<String,Object> summaryMap = converDataToMap(summary,"总和");
        res.add(summaryMap);
        Map<String,Object> result = new HashMap<>();
        result.put("list",res);
        result.put("summary",summaryMap);
        return AjaxResult.success(result);
    }

    /**
     * 查询SysWeeklyZcmplan列表
     */
    @GetMapping("/list")
    public TableDataInfo list(SysWeeklyZcmplan sysWeeklyZcmplan) {
        startPage();
        List<SysWeeklyZcmplan> list = sysWeeklyZcmplanService.selectSysWeeklyZcmplanList(sysWeeklyZcmplan);
        return getDataTable(list);
    }

    /**
     * 导出SysWeeklyZcmplan列表
     */
    @Log(title = "SysWeeklyZcmplan", businessType = BusinessType.EXPORT)
    @PostMapping("/export")
    public void export(HttpServletResponse response, SysWeeklyZcmplan sysWeeklyZcmplan) {
        List<SysWeeklyZcmplan> list = sysWeeklyZcmplanService.selectSysWeeklyZcmplanList(sysWeeklyZcmplan);
        ExcelUtil<SysWeeklyZcmplan> util = new ExcelUtil<SysWeeklyZcmplan>(SysWeeklyZcmplan.class);
        util.exportExcel(response, list, "SysWeeklyZcmplan数据");
    }

    /**
     * 获取SysWeeklyZcmplan详细信息
     */
    @GetMapping(value = "/{wkYear}")
    public AjaxResult getInfo(@PathVariable("wkYear") String wkYear) {
        return success(sysWeeklyZcmplanService.selectSysWeeklyZcmplanByWkYear(wkYear));
    }

    /**
     * 新增SysWeeklyZcmplan
     */
    @Log(title = "SysWeeklyZcmplan", businessType = BusinessType.INSERT)
    @PostMapping
    public AjaxResult add(@RequestBody SysWeeklyZcmplan sysWeeklyZcmplan) {
        return toAjax(sysWeeklyZcmplanService.insertSysWeeklyZcmplan(sysWeeklyZcmplan));
    }

    /**
     * 修改SysWeeklyZcmplan
     */
    @Log(title = "SysWeeklyZcmplan", businessType = BusinessType.UPDATE)
    @PutMapping
    public AjaxResult edit(@RequestBody SysWeeklyZcmplan sysWeeklyZcmplan) {
        return toAjax(sysWeeklyZcmplanService.updateSysWeeklyZcmplan(sysWeeklyZcmplan));
    }

    /**
     * 删除SysWeeklyZcmplan
     */
    @Log(title = "SysWeeklyZcmplan", businessType = BusinessType.DELETE)
	@DeleteMapping("/{wkYears}")
    public AjaxResult remove(@PathVariable String[] wkYears) {
        return toAjax(sysWeeklyZcmplanService.deleteSysWeeklyZcmplanByWkYears(wkYears));
    }
}
